
package model;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import utils.Constants;

public class DatabaseModel {
    
    // Singleton
    private static DatabaseModel instance;
    
    private static Connection connection;
    private static Statement statement;
    
    public DatabaseModel() {
        connection = null;
        statement = null;
        try {
            DatabaseModel.open(Constants.DATABASE_NAME);
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
    }
    
    public static DatabaseModel getInstance() {
        if (instance == null)
            instance = new DatabaseModel();
        return instance;
    }
    
    private static void open(String databaseName) throws Exception {
        
        File file = new File(databaseName);
        boolean newDatabase = true;
        
        if (file.exists()) {
            newDatabase = false;
            System.out.println("This database name already exists");
        } else {
            System.out.println("This database name does not exist");
        }
        
        Class.forName("org.sqlite.JDBC");
        connection = DriverManager.getConnection("jdbc:sqlite:"
                + databaseName);
        System.out.println("Opened database successfully");
        statement = connection.createStatement();
        
        if (newDatabase) {
            createTable();
        }
    }
    
    private static void createTable() throws SQLException {
        
        statement = connection.createStatement();
        String sql = Constants.DATABASE_TABLE_CREATE;
        statement.executeUpdate(sql);
        
    }
    
    public synchronized static void insert(String nodeName, String fileName,
            String md5) {
        
        String sql = "INSERT INTO " + Constants.DATABASE_TABLE_NAME + " ("
                + Constants.DATABASE_NODE + ", "
                + Constants.DATABASE_FILE + ", "
                + Constants.DATABASE_MD5 + ") "
                + "VALUES ("
                + "'" + nodeName + "', "
                + "'" + fileName + "',"
                + "'" + md5 + "');";
        
        try {
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public synchronized static void remove(String clientName, String fileName) {
        String sql = "DELETE FROM " + Constants.DATABASE_TABLE_NAME + " WHERE "
                + Constants.DATABASE_NODE + "='" + clientName + "' AND "
                + Constants.DATABASE_FILE + "='" + fileName + "';";
        
        try {
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static boolean checkIfFileExists(String fileName) {
        ResultSet resultSet;
        boolean returnValue = false;
        try {
            resultSet = statement.executeQuery("SELECT DISTINCT "
                    + Constants.DATABASE_FILE
                    + " FROM " + Constants.DATABASE_TABLE_NAME + ";");
            while (resultSet.next()) {
                if (resultSet.getString(Constants.DATABASE_FILE)
                        .equalsIgnoreCase(fileName)) {
                    returnValue = true;
                }
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return returnValue;
    }
    
    public static boolean checkIfFileAvailable(String fileName,
            String[] connectedClients) {
        ResultSet resultSet;
        String query;
        boolean returnValue = false;
        query = "SELECT DISTINCT "
                + Constants.DATABASE_FILE
                + " FROM " + Constants.DATABASE_TABLE_NAME + " WHERE "
                + Constants.DATABASE_NODE + " IN (";
        for (int i = 0; i < connectedClients.length; i++) {
            query = query + "'" + connectedClients[i] + "'";
            if (i != connectedClients.length - 1) {
                query = query + ",";
            }
        }
        query = query + ");";
        System.out.println(query);
        try {
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                if (resultSet.getString(Constants.DATABASE_FILE)
                        .equalsIgnoreCase(fileName)) {
                    returnValue = true;
                }
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return returnValue;
    }
    
    public static String getClientAvailable(String fileName,
            String[] connectedClients) {
        ResultSet resultSet;
        String query;
        String returnValue = "";
        query = "SELECT * FROM "
                + Constants.DATABASE_TABLE_NAME + " WHERE "
                + Constants.DATABASE_NODE + " IN (";
        for (int i = 0; i < connectedClients.length; i++) {
            query = query + "'" + connectedClients[i] + "'";
            if (i != connectedClients.length - 1) {
                query = query + ",";
            }
        }
        query = query + ") AND " + Constants.DATABASE_FILE + " ='" + fileName
                + "';";
        System.out.println(query);
        try {
            resultSet = statement.executeQuery(query);
            if (resultSet.next()) {
                returnValue = resultSet.getString(Constants.DATABASE_NODE);
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return returnValue;
    }
    
    public static String[] getClientsThatHave(String fileName,
            String[] connectedClients) {
        ResultSet resultSet = null;
        String query;
        ArrayList<String> returnArray = new ArrayList<String>();
        query = "SELECT * FROM "
                + Constants.DATABASE_TABLE_NAME + " WHERE "
                + Constants.DATABASE_NODE + " IN (";
        for (int i = 0; i < connectedClients.length; i++) {
            query = query + "'" + connectedClients[i] + "'";
            if (i != connectedClients.length - 1) {
                query = query + ",";
            }
        }
        query = query + ") AND " + Constants.DATABASE_FILE + " ='" + fileName
                + "';";
        System.out.println(query);
        try {
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                if (resultSet.getString(Constants.DATABASE_FILE)
                        .equalsIgnoreCase(fileName)) {
                    returnArray.add(resultSet
                            .getString(Constants.DATABASE_NODE));
                }
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String[] returnValue = new String[returnArray.size()];
        returnValue = returnArray.toArray(returnValue);
        return returnValue;
    }
    
    public static boolean checkIfClientHas(String clientName, String fileName) {
        ResultSet resultSet = null;
        String query;
        boolean returnValue = false;
        query = "SELECT * FROM "
                + Constants.DATABASE_TABLE_NAME + " WHERE "
                + Constants.DATABASE_NODE + "='" + clientName + "' AND "
                + Constants.DATABASE_FILE + "='" + fileName
                + "';";
        System.out.println(query);
        try {
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                if (resultSet.getString(Constants.DATABASE_NODE)
                        .equalsIgnoreCase(clientName)) {
                    returnValue = true;
                }
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return returnValue;
    }
    
}
